﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Loja_Virtual_Livraria.App_Code.Model;
using Loja_Virtual_Livraria.App_Code.BLL;

namespace Loja_Virtual_Livraria.App_Code.SQLServerDAL
{
    public class VendaDAL
    {
        public static Venda selectVendaAbertaByPessoaId(int pessoaID)
        {
            SqlConnection myConnection = null;
            SqlDataReader myDataReader = null;
            Venda retVenda = null;

            try
            {
                myConnection = SQLServerConnection.getConnection();
                SqlCommand myCommand = new SqlCommand("SELECT * FROM Venda WHERE estado = 0 AND WHERE pessoaID = @pessoaID", myConnection);

                SqlParameter paramId = new SqlParameter("pessoaID", SqlDbType.Int);
                paramId.Direction = ParameterDirection.Input;
                paramId.Value = pessoaID;

                myCommand.Parameters.Add(paramId);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader();

                if (myDataReader.Read())
                {
                    retVenda = VendaBLL.newVenda();
                    retVenda.setVendaID(Convert.ToInt32(myDataReader["vendaID"].ToString()));
                    retVenda.setEstado(Convert.ToInt32(myDataReader["estado"].ToString()));
                    retVenda.setPessoaID(Convert.ToInt32(myDataReader["pessoaID"].ToString()));
                    retVenda.setDataVenda(Convert.ToDateTime(myDataReader["dataVenda"].ToString()));
                }
            }
            finally
            {
                if (myConnection != null)
                    myConnection.Close();

                if (myDataReader != null)
                    myDataReader.Close();
            }
            return retVenda;
        }

        public static void insertVenda(Venda myVenda)
        {
            SqlConnection myConnection = null;
            try
            {
                myConnection = SQLServerConnection.getConnection();
                SqlCommand myCommand = new SqlCommand("INSERT INTO Venda (pessoaID, estado, dataVenda) " +
                "VALUES (@pessoaID, @estado, @dataVenda)", myConnection);

                SqlParameter paramPessoaId = new SqlParameter("pessoaID", SqlDbType.Int);
                paramPessoaId.Direction = ParameterDirection.Input;
                paramPessoaId.Value = myVenda.getPessoaID();

                SqlParameter paramEstado = new SqlParameter("estado", SqlDbType.Int);
                paramEstado.Direction = ParameterDirection.Input;
                paramEstado.Value = myVenda.getEstado();

                SqlParameter paramDataVenda = new SqlParameter("dataVenda", SqlDbType.DateTime);
                paramDataVenda.Direction = ParameterDirection.Input;
                paramDataVenda.Value = myVenda.getDataVenda();

                myCommand.Parameters.Add(paramEstado);
                myCommand.Parameters.Add(paramPessoaId);
                myCommand.Parameters.Add(paramDataVenda);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                if (myConnection != null)
                    myConnection.Close();
            }
        }

        public static void updateEstadoVenda(Venda myVenda)
        {
            SqlConnection myConnection = null;
            try
            {
                myConnection = SQLServerConnection.getConnection();
                SqlCommand myCommand = new SqlCommand("UPDATE Venda SET estado = @estado WHERE vendaID = @vendaID", myConnection);

                SqlParameter paramEstado = new SqlParameter("estado", SqlDbType.Int);
                paramEstado.Direction = ParameterDirection.Input;
                paramEstado.Value = myVenda.getEstado();

                SqlParameter paramVendaId = new SqlParameter("vendaID", SqlDbType.Int);
                paramVendaId.Direction = ParameterDirection.Input;
                paramVendaId.Value = myVenda.getVendaID();


                myCommand.Parameters.Add(paramEstado);
                myCommand.Parameters.Add(paramVendaId);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                if (myConnection != null)
                    myConnection.Close();
            }
        }
    }
}